<?php

	require_once("includes/config.php");
	require_once("includes/functions.php");
	require_once("classes/class.Team.php");
	require_once("classes/class.Field.php");
	require_once("classes/class.User.php");
	require_once("classes/class.Metric.php");

	$db_link = mysql_connect($db_host,$db_username,$db_password);
	mysql_select_db($db_database) or die("Unable to select database");

	$db_query = "DROP TABLE IF EXISTS report;";
	$db_result = mysql_query($db_query);
	@$db_count = mysql_num_rows($db_result);
		
	$db_query = "SELECT * FROM field ORDER BY team_name, field_order;";
	$db_result = mysql_query($db_query);
	@$db_count = mysql_num_rows($db_result);
	
	$array_fields = array();
	while($db_i<$db_count) {
		$field_name = mysql_result($db_result,$db_i,"field_name");
		$field_type = mysql_result($db_result,$db_i,"field_type");
		array_push($array_fields,array(field_name=>$field_name,field_type=>$field_type));	
		$db_i++;
	}	


	$db_query = "CREATE TABLE report ( ";
	$db_query .= " team_name VARCHAR ( 50 ) NOT NULL,";
	$db_query .= " user_id VARCHAR ( 50 ) NOT NULL,";
	$db_query .= " datestamp DATE NOT NULL,";
	foreach($array_fields as $field) {
		if($field["field_type"]=="TEXT") {
			$db_query .= "" . $field["field_name"] ." VARCHAR ( 255 ) NOT NULL,";
		}
		elseif($field["field_type"]=="NUMBER") {
			$db_query .= "" . $field["field_name"] ." INT NOT NULL,";
		}
		elseif($field["field_type"]=="DATE") {
			$db_query .= "" . $field["field_name"] ." DATE NOT NULL,";
		}
		else {
			$db_query .= "" . $field["field_name"] ." VARCHAR ( 255 ) NOT NULL,";
		}
	}
	$db_query = substr($db_query,0,-1);
	$db_query .= " )";
	$db_result = mysql_query($db_query);
	@$db_count = mysql_num_rows($db_result);	
	
	foreach($array_fields as $field) {
		//echo $field["field_name"] . "<br/>";
	
	}
	
	$db_query = "SELECT * FROM metrics WHERE 1 ";

	$db_query .= " ORDER BY team_name, user_id, datestamp;";
	$db_query .= ";";

	$db_i = 0;

	$db_result = mysql_query($db_query);
	@$db_count = mysql_num_rows($db_result);

	$first_run = true;

	while($db_i<$db_count) {

		$metric_id = mysql_result($db_result,$db_i,"metric_id");
		$user_id = mysql_result($db_result,$db_i,"user_id");
		$team_name = mysql_result($db_result,$db_i,"team_name");
		$datestamp = mysql_result($db_result,$db_i,"datestamp");
		$field_name = mysql_result($db_result,$db_i,"field_name");
		$value = mysql_result($db_result,$db_i,"value");	
		$current_metric_code = $user_id . "_" . $team_name . "_" . $datestamp;

		if($current_metric_code!=$previous_metric_code or $first_run==true) {
			
			$first_run = false;
			
			// start of new metric group
			
			//echo $current_metric_code . "<br/>";
			
			$sql_insert = "INSERT INTO report (team_name,user_id,datestamp";
			$sql_values = "VALUES ('$team_name','$user_id','$datestamp'";
			
			foreach($array_fields as $field) {
			
				$sql_insert.= ",".$field["field_name"];
			
				$obj_field = new Field();
				$obj_field->Load($field["field_name"]);
			
				$obj_metric = new Metric();
				$obj_metric->Load($user_id,$team_name,$datestamp,$field["field_name"]);
				
				$metric_value = $obj_metric->GetValue();
				if($metric_value=="") {$metric_value=0;}
				
				if($obj_field->GetType()=="NUMBER") {
					$sql_values .= ",".$metric_value;
				} else {
					$sql_values .= ",'".$metric_value."'";	
				}
				
			}
			
			$sql_insert .= ") ";
			$sql_values .= ") ";
			$sql_query = $sql_insert . $sql_values . ";";
			//echo $sql_query . "<br/><hr/>";

			$db_insert_query = $sql_query;
			$db_insert_result = mysql_query($db_insert_query,$db_link);
			//echo mysql_error($db_link);
			
		}
							
		$db_i++;
		$previous_metric_code = $user_id . "_" . $team_name . "_" . $datestamp;
		
	}	

?>